--    Program: 		CAPARIMP_CUSTOMER_ACCOUNT.sql
--    Description:  Import AR CUSTOMER ACCOUNT information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         1-Feb-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp 			varchar2(2000);
   
   l_cust_account_id		number;
   l_account_number			varchar2(30);
   l_orig_system_reference	varchar2(240);
                    		                 		
   cursor cur_ar_customer_account is
      select * from CRST_HZ_CUST_ACCOUNTS
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_ar_customer_account loop   	
     l_err_msg_tmp := '';
     
     SELECT HZ_CUST_ACCOUNTS_S.NEXTVAL 
     INTO l_cust_account_id
     FROM DUAL;
     
     update HZ_CUST_ACCOUNTS
     set    account_number = substr(account_number||'X', 1, 30),
            orig_system_reference = substr(orig_system_reference||'X', 1, 240)
     where  cust_account_id = cur.CUST_ACCOUNT_ID;
     
     update CRST_HZ_CUST_ACCOUNTS
     set    cust_account_id_n = l_cust_account_id,
            account_number_n = cur.account_number,
            orig_system_reference_n = cur.orig_system_reference
     where  cust_account_id = cur.cust_account_id; 
     
     begin
       -- import data
       insert into HZ_CUST_ACCOUNTS
       (
              CUST_ACCOUNT_ID                 ,
              PARTY_ID                        ,
              LAST_UPDATE_DATE                ,
              ACCOUNT_NUMBER                  ,
              LAST_UPDATED_BY                 ,
              CREATION_DATE                   ,
              CREATED_BY                      ,
              LAST_UPDATE_LOGIN               ,
              REQUEST_ID                      ,
              PROGRAM_APPLICATION_ID          ,
              PROGRAM_ID                      ,
              PROGRAM_UPDATE_DATE             ,
              WH_UPDATE_DATE                  ,
              ATTRIBUTE_CATEGORY              ,
              ATTRIBUTE1                      ,
              ATTRIBUTE2                      ,
              ATTRIBUTE3                      ,
              ATTRIBUTE4                      ,
              ATTRIBUTE5                      ,
              ATTRIBUTE6                      ,
              ATTRIBUTE7                      ,
              ATTRIBUTE8                      ,
              ATTRIBUTE9                      ,
              ATTRIBUTE10                     ,
              ATTRIBUTE11                     ,
              ATTRIBUTE12                     ,
              ATTRIBUTE13                     ,
              ATTRIBUTE14                     ,
              ATTRIBUTE15                     ,
              ATTRIBUTE16                     ,
              ATTRIBUTE17                     ,
              ATTRIBUTE18                     ,
              ATTRIBUTE19                     ,
              ATTRIBUTE20                     ,
              GLOBAL_ATTRIBUTE_CATEGORY       ,
              GLOBAL_ATTRIBUTE1               ,
              GLOBAL_ATTRIBUTE2               ,
              GLOBAL_ATTRIBUTE3               ,
              GLOBAL_ATTRIBUTE4               ,
              GLOBAL_ATTRIBUTE5               ,
              GLOBAL_ATTRIBUTE6               ,
              GLOBAL_ATTRIBUTE7               ,
              GLOBAL_ATTRIBUTE8               ,
              GLOBAL_ATTRIBUTE9               ,
              GLOBAL_ATTRIBUTE10              ,
              GLOBAL_ATTRIBUTE11              ,
              GLOBAL_ATTRIBUTE12              ,
              GLOBAL_ATTRIBUTE13              ,
              GLOBAL_ATTRIBUTE14              ,
              GLOBAL_ATTRIBUTE15              ,
              GLOBAL_ATTRIBUTE16              ,
              GLOBAL_ATTRIBUTE17              ,
              GLOBAL_ATTRIBUTE18              ,
              GLOBAL_ATTRIBUTE19              ,
              GLOBAL_ATTRIBUTE20              ,
              ORIG_SYSTEM_REFERENCE           ,
              STATUS                          ,
              CUSTOMER_TYPE                   ,
              CUSTOMER_CLASS_CODE             ,
              PRIMARY_SALESREP_ID             ,
              SALES_CHANNEL_CODE              ,
              ORDER_TYPE_ID                   ,
              PRICE_LIST_ID                   ,
              SUBCATEGORY_CODE                ,
              TAX_CODE                        ,
              FOB_POINT                       ,
              FREIGHT_TERM                    ,
              SHIP_PARTIAL                    ,
              SHIP_VIA                        ,
              WAREHOUSE_ID                    ,
              PAYMENT_TERM_ID                 ,
              TAX_HEADER_LEVEL_FLAG           ,
              TAX_ROUNDING_RULE               ,
              COTERMINATE_DAY_MONTH           ,
              PRIMARY_SPECIALIST_ID           ,
              SECONDARY_SPECIALIST_ID         ,
              ACCOUNT_LIABLE_FLAG             ,
              RESTRICTION_LIMIT_AMOUNT        ,
              CURRENT_BALANCE                 ,
              PASSWORD_TEXT                   ,
              HIGH_PRIORITY_INDICATOR         ,
              ACCOUNT_ESTABLISHED_DATE        ,
              ACCOUNT_TERMINATION_DATE        ,
              ACCOUNT_ACTIVATION_DATE         ,
              CREDIT_CLASSIFICATION_CODE      ,
              DEPARTMENT                      ,
              MAJOR_ACCOUNT_NUMBER            ,
              HOTWATCH_SERVICE_FLAG           ,
              HOTWATCH_SVC_BAL_IND            ,
              HELD_BILL_EXPIRATION_DATE       ,
              HOLD_BILL_FLAG                  ,
              HIGH_PRIORITY_REMARKS           ,
              PO_EFFECTIVE_DATE               ,
              PO_EXPIRATION_DATE              ,
              REALTIME_RATE_FLAG              ,
              SINGLE_USER_FLAG                ,
              WATCH_ACCOUNT_FLAG              ,
              WATCH_BALANCE_INDICATOR         ,
              GEO_CODE                        ,
              ACCT_LIFE_CYCLE_STATUS          ,
              ACCOUNT_NAME                    ,
              DEPOSIT_REFUND_METHOD           ,
              DORMANT_ACCOUNT_FLAG            ,
              NPA_NUMBER                      ,
              PIN_NUMBER                      ,
              SUSPENSION_DATE                 ,
              WRITE_OFF_ADJUSTMENT_AMOUNT     ,
              WRITE_OFF_PAYMENT_AMOUNT        ,
              WRITE_OFF_AMOUNT                ,
              SOURCE_CODE                     ,
              COMPETITOR_TYPE                 ,
              COMMENTS                        ,
              DATES_NEGATIVE_TOLERANCE        ,
              DATES_POSITIVE_TOLERANCE        ,
              DATE_TYPE_PREFERENCE            ,
              OVER_SHIPMENT_TOLERANCE         ,
              UNDER_SHIPMENT_TOLERANCE        ,
              OVER_RETURN_TOLERANCE           ,
              UNDER_RETURN_TOLERANCE          ,
              ITEM_CROSS_REF_PREF             ,
              SHIP_SETS_INCLUDE_LINES_FLAG    ,
              ARRIVALSETS_INCLUDE_LINES_FLAG  ,
              SCHED_DATE_PUSH_FLAG            ,
              INVOICE_QUANTITY_RULE           ,
              PRICING_EVENT                   ,
              ACCOUNT_REPLICATION_KEY         ,
              STATUS_UPDATE_DATE              ,
              AUTOPAY_FLAG                    ,
              NOTIFY_FLAG                     ,
              LAST_BATCH_ID                   ,
              ORG_ID                          ,
              OBJECT_VERSION_NUMBER           ,
              CREATED_BY_MODULE               ,
              APPLICATION_ID                  ,
              SELLING_PARTY_ID                
       )
       values
       (
            l_cust_account_id                   ,
            CAPARIMP_MAPPING.GetHZPartyIDMapping(cur.PARTY_ID),
            cur.LAST_UPDATE_DATE                ,
            cur.ACCOUNT_NUMBER                  ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
            cur.CREATION_DATE                   ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            cur.LAST_UPDATE_LOGIN               ,
            cur.REQUEST_ID                      ,
            cur.PROGRAM_APPLICATION_ID          ,
            cur.PROGRAM_ID                      ,
            cur.PROGRAM_UPDATE_DATE             ,
            cur.WH_UPDATE_DATE                  ,
            cur.ATTRIBUTE_CATEGORY              ,
            cur.ATTRIBUTE1                      ,
            cur.ATTRIBUTE2                      ,
            cur.ATTRIBUTE3                      ,
            cur.ATTRIBUTE4                      ,
            cur.ATTRIBUTE5                      ,
            cur.ATTRIBUTE6                      ,
            cur.ATTRIBUTE7                      ,
            cur.ATTRIBUTE8                      ,
            cur.ATTRIBUTE9                      ,
            cur.ATTRIBUTE10                     ,
            cur.ATTRIBUTE11                     ,
            cur.ATTRIBUTE12                     ,
            cur.ATTRIBUTE13                     ,
            cur.ATTRIBUTE14                     ,
            cur.ATTRIBUTE15                     ,
            cur.ATTRIBUTE16                     ,
            cur.ATTRIBUTE17                     ,
            cur.ATTRIBUTE18                     ,
            cur.ATTRIBUTE19                     ,
            cur.ATTRIBUTE20                     ,
            cur.GLOBAL_ATTRIBUTE_CATEGORY       ,
            cur.GLOBAL_ATTRIBUTE1               ,
            cur.GLOBAL_ATTRIBUTE2               ,
            cur.GLOBAL_ATTRIBUTE3               ,
            cur.GLOBAL_ATTRIBUTE4               ,
            cur.GLOBAL_ATTRIBUTE5               ,
            cur.GLOBAL_ATTRIBUTE6               ,
            cur.GLOBAL_ATTRIBUTE7               ,
            cur.GLOBAL_ATTRIBUTE8               ,
            cur.GLOBAL_ATTRIBUTE9               ,
            cur.GLOBAL_ATTRIBUTE10              ,
            cur.GLOBAL_ATTRIBUTE11              ,
            cur.GLOBAL_ATTRIBUTE12              ,
            cur.GLOBAL_ATTRIBUTE13              ,
            cur.GLOBAL_ATTRIBUTE14              ,
            cur.GLOBAL_ATTRIBUTE15              ,
            cur.GLOBAL_ATTRIBUTE16              ,
            cur.GLOBAL_ATTRIBUTE17              ,
            cur.GLOBAL_ATTRIBUTE18              ,
            cur.GLOBAL_ATTRIBUTE19              ,
            cur.GLOBAL_ATTRIBUTE20              ,
            cur.ORIG_SYSTEM_REFERENCE           ,
            cur.STATUS                          ,
            cur.CUSTOMER_TYPE                   ,
            cur.CUSTOMER_CLASS_CODE             ,
            cur.PRIMARY_SALESREP_ID             ,
            cur.SALES_CHANNEL_CODE              ,
            cur.ORDER_TYPE_ID                   ,
            cur.PRICE_LIST_ID                   ,
            cur.SUBCATEGORY_CODE                ,
            cur.TAX_CODE                        ,
            cur.FOB_POINT                       ,
            cur.FREIGHT_TERM                    ,
            cur.SHIP_PARTIAL                    ,
            cur.SHIP_VIA                        ,
            cur.WAREHOUSE_ID                    ,
            cur.PAYMENT_TERM_ID                 ,
            cur.TAX_HEADER_LEVEL_FLAG           ,
            cur.TAX_ROUNDING_RULE               ,
            cur.COTERMINATE_DAY_MONTH           ,
            cur.PRIMARY_SPECIALIST_ID           ,
            cur.SECONDARY_SPECIALIST_ID         ,
            cur.ACCOUNT_LIABLE_FLAG             ,
            cur.RESTRICTION_LIMIT_AMOUNT        ,
            cur.CURRENT_BALANCE                 ,
            cur.PASSWORD_TEXT                   ,
            cur.HIGH_PRIORITY_INDICATOR         ,
            cur.ACCOUNT_ESTABLISHED_DATE        ,
            cur.ACCOUNT_TERMINATION_DATE        ,
            cur.ACCOUNT_ACTIVATION_DATE         ,
            cur.CREDIT_CLASSIFICATION_CODE      ,
            cur.DEPARTMENT                      ,
            cur.MAJOR_ACCOUNT_NUMBER            ,
            cur.HOTWATCH_SERVICE_FLAG           ,
            cur.HOTWATCH_SVC_BAL_IND            ,
            cur.HELD_BILL_EXPIRATION_DATE       ,
            cur.HOLD_BILL_FLAG                  ,
            cur.HIGH_PRIORITY_REMARKS           ,
            cur.PO_EFFECTIVE_DATE               ,
            cur.PO_EXPIRATION_DATE              ,
            cur.REALTIME_RATE_FLAG              ,
            cur.SINGLE_USER_FLAG                ,
            cur.WATCH_ACCOUNT_FLAG              ,
            cur.WATCH_BALANCE_INDICATOR         ,
            cur.GEO_CODE                        ,
            cur.ACCT_LIFE_CYCLE_STATUS          ,
            cur.ACCOUNT_NAME                    ,
            cur.DEPOSIT_REFUND_METHOD           ,
            cur.DORMANT_ACCOUNT_FLAG            ,
            cur.NPA_NUMBER                      ,
            cur.PIN_NUMBER                      ,
            cur.SUSPENSION_DATE                 ,
            cur.WRITE_OFF_ADJUSTMENT_AMOUNT     ,
            cur.WRITE_OFF_PAYMENT_AMOUNT        ,
            cur.WRITE_OFF_AMOUNT                ,
            cur.SOURCE_CODE                     ,
            cur.COMPETITOR_TYPE                 ,
            cur.COMMENTS                        ,
            cur.DATES_NEGATIVE_TOLERANCE        ,
            cur.DATES_POSITIVE_TOLERANCE        ,
            cur.DATE_TYPE_PREFERENCE            ,
            cur.OVER_SHIPMENT_TOLERANCE         ,
            cur.UNDER_SHIPMENT_TOLERANCE        ,
            cur.OVER_RETURN_TOLERANCE           ,
            cur.UNDER_RETURN_TOLERANCE          ,
            cur.ITEM_CROSS_REF_PREF             ,
            cur.SHIP_SETS_INCLUDE_LINES_FLAG    ,
            cur.ARRIVALSETS_INCLUDE_LINES_FLAG  ,
            cur.SCHED_DATE_PUSH_FLAG            ,
            cur.INVOICE_QUANTITY_RULE           ,
            cur.PRICING_EVENT                   ,
            cur.ACCOUNT_REPLICATION_KEY         ,
            cur.STATUS_UPDATE_DATE              ,
            cur.AUTOPAY_FLAG                    ,
            cur.NOTIFY_FLAG                     ,
            cur.LAST_BATCH_ID                   ,
            CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
            cur.OBJECT_VERSION_NUMBER           ,
            cur.CREATED_BY_MODULE               ,
            cur.APPLICATION_ID                  ,
            cur.SELLING_PARTY_ID                                  
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;  	  
   end loop;
   
   if l_rejected_count <> 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_CUSTOMER_ACCOUNT', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
